﻿import sqlite3

DB_FILE = 'example.db'
IGNORE_TABLE = 'ignore'
SYNC_HISTORY_TABLE = 'sync'

CREATE_IGNORE_TABLE_SQL = """CREATE TABLE ignore(
    filename VARCHAR(255),
    remote_path VARCHAR(255),
    local_path VARCHAR(255)
)""".format(IGNORE_TABLE)

CREATE_SYNC_HISTORY_TABLE_SQL = """CREATE TABLE sync(
    filename VARCHAR(255),
    sync_operation VARCHAR(255),
    operation_time VARCHAR(255)
)""".format(SYNC_HISTORY_TABLE)


def init_db():
    print(6666)
    # 创建数据库连接
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    # 创建忽略表
    cursor.execute(CREATE_IGNORE_TABLE_SQL)
    print(f'表格 {IGNORE_TABLE} 创建成功！')

    # 创建同步表
    cursor.execute(CREATE_SYNC_HISTORY_TABLE_SQL)
    print(f'表格 {SYNC_HISTORY_TABLE} 创建成功！')

    # 提交修改并关闭连接
    conn.commit()
    cursor.close()
    conn.close()


def add_to_ignore_list(filename, remote_path=None, local_path=None):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    query_sql = f"SELECT * FROM {IGNORE_TABLE} WHERE filename=?"
    cursor.execute(query_sql, (filename,))
    result = cursor.fetchone()
    if result is not None:
        print(f"{filename} 已经存在于忽略列表中！")
        return False

    if (remote_path is None) and (local_path is None):
        print(f"未指定 {filename} 的路径，将默认忽略服务器和本地的该文件")
        delete_sql = f"DELETE FROM {IGNORE_TABLE} WHERE filename=?"
        cursor.execute(delete_sql, (filename,))
        insert_sql = f"INSERT INTO {IGNORE_TABLE} VALUES (?, ?, ?)"
        cursor.execute(insert_sql, (filename, '', ''))
    else:
        insert_sql = f"INSERT INTO {IGNORE_TABLE} VALUES (?, ?, ?)"
        cursor.execute(insert_sql, (filename, remote_path, local_path))

    conn.commit()
    cursor.close()
    conn.close()
    print(f"{filename} 已成功添加到忽略列表！")
    return True


def remove_from_ignore_list(filename, remote_path, local_path):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    delete_sql = f"DELETE FROM {IGNORE_TABLE} WHERE filename=? AND remote_path=? AND local_path=?"
    cursor.execute(delete_sql, (filename, remote_path, local_path))

    conn.commit()
    cursor.close()
    conn.close()
    print(f"{filename} 已从忽略列表中删除！")


def update_ignore_list(filename, remote_path=None, local_path=None):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    query_sql = f"SELECT * FROM {IGNORE_TABLE} WHERE filename=?"
    cursor.execute(query_sql, (filename,))
    result = cursor.fetchone()
    if result is None:
        print(f"{filename} 不存在于忽略列表中！")
        return False

    if remote_path is not None:
        update_sql = f"UPDATE {IGNORE_TABLE} SET remote_path=? WHERE filename=?"
        cursor.execute(update_sql, (remote_path, filename))
    if local_path is not None:
        update_sql = f"UPDATE {IGNORE_TABLE} SET local_path=? WHERE filename=?"
        cursor.execute(update_sql, (local_path, filename))

    conn.commit()
    cursor.close()
    conn.close()
    print(f"{filename} 的路径信息已更新！")
    return True


def query_ignore_list(filename=None, remote_path=None, local_path=None):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    if (filename is None) and (remote_path is None) and (local_path is None):
        query_sql = f"SELECT * FROM {IGNORE_TABLE}"
        cursor.execute(query_sql)
    else:
        query_sql = f"SELECT * FROM {IGNORE_TABLE} WHERE filename=? OR remote_path=? OR local_path=?"
        cursor.execute(query_sql, (filename, remote_path, local_path))
    result = cursor.fetchall()

    cursor.close()
    conn.close()
    return result


def add_to_sync_history(filename, sync_operation, operation_time):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()
    insert_sql = f"INSERT INTO {SYNC_HISTORY_TABLE} VALUES (?, ?, ?)"
    cursor.execute(insert_sql, (filename, sync_operation, operation_time))

    conn.commit()
    cursor.close()
    conn.close()
    print(f"{filename} 的同步历史记录已添加！")


def query_sync_history(filename=None):
    conn = sqlite3.connect(DB_FILE)
    cursor = conn.cursor()

    if filename is None:
        query_sql = f"SELECT * FROM {SYNC_HISTORY_TABLE}"
        cursor.execute(query_sql)

    else:
        query_sql = f"SELECT * FROM {SYNC_HISTORY_TABLE} WHERE filename=?"
        cursor.execute(query_sql, (filename,))
        result = cursor.fetchall()

        cursor.close()
        conn.close()
        return result


if __name__ == '__main__':
    init_db()
    print(query_ignore_list('a', 'a',None))
